package com.fsbm.ctech.maxis.ebpsbackend;

import java.util.*;
import java.sql.*;
import java.text.*;
import com.fsbm.ctech.maxis.ebpsbackend.ConfigObject;

/**
 * 
 * @author Meau
 */

public class GetStatement {
	static ConfigObject cfgObj = new ConfigObject();

	public static void main(String[] args) {
		ArrayList arrStmtRunIds = new ArrayList();
		Vector vecStatementIds = new Vector();

		try {

			arrStmtRunIds.add("2321");
			/*
			 * arrStmtRunIds.add("1270"); arrStmtRunIds.add("1272");
			 * arrStmtRunIds.add("1273"); arrStmtRunIds.add("1274");
			 * arrStmtRunIds.add("1275"); arrStmtRunIds.add("1277");
			 * arrStmtRunIds.add("1279"); arrStmtRunIds.add("1280");
			 * arrStmtRunIds.add("1281"); arrStmtRunIds.add("1283");
			 * arrStmtRunIds.add("1284"); arrStmtRunIds.add("1285");
			 * arrStmtRunIds.add("1286"); arrStmtRunIds.add("1287");
			 * arrStmtRunIds.add("1288"); arrStmtRunIds.add("1290");
			 * arrStmtRunIds.add("1291"); arrStmtRunIds.add("1292");
			 * arrStmtRunIds.add("1293"); arrStmtRunIds.add("1294");
			 * arrStmtRunIds.add("1295"); arrStmtRunIds.add("1296");
			 * arrStmtRunIds.add("1297"); arrStmtRunIds.add("1298");
			 * arrStmtRunIds.add("1299"); arrStmtRunIds.add("1300");
			 * arrStmtRunIds.add("1303"); arrStmtRunIds.add("1304");
			 * 
			 * arrStmtRunIds.add("814"); arrStmtRunIds.add("818");
			 * arrStmtRunIds.add("819"); arrStmtRunIds.add("820");
			 * arrStmtRunIds.add("821"); arrStmtRunIds.add("822");
			 * arrStmtRunIds.add("823");
			 */

			java.util.Date startTime = new java.util.Date();
			System.out.println("\nStart time:" + startTime);

			vecStatementIds = getAllStmtIds(arrStmtRunIds);
			int vSize = vecStatementIds.size();
			for (int x = 0; x < vSize; ++x) {
				String strStmtId = (String) vecStatementIds.get(x);
				System.out.println("Displaying stmt ID in the vectors...."
						+ strStmtId);

			}
			java.util.Date endTime = new java.util.Date();
			System.out.println("\nEnd time:" + endTime);

		} catch (Exception e) {
			System.out.println("Exception is..." + e);
		}
	}

	public static Vector splitStatementIds(Vector vecStmtIds, String activity) {
		Vector vecStatementIds = new Vector();
		int vsize = vecStmtIds.size();
		int maxRowPerCSV = cfgObj.maxSubsRowPerCSV;
		int intNoOfCSVFile = 0;
		String strFileName = "";

		if (activity.startsWith("BillReprintDeliver")) {
			maxRowPerCSV = cfgObj.maxReprintRowPerCSV;
		}

		if (vsize % maxRowPerCSV == 0) {
			intNoOfCSVFile = vsize / maxRowPerCSV;
		} else {
			intNoOfCSVFile = (vsize / maxRowPerCSV) + 1;
		}

		for (int i = 0; i < intNoOfCSVFile; i++) {
			SplitStatementIdsDataBean dataBean = new SplitStatementIdsDataBean();

			DateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss'X'");
			if (activity.equals("Subscription")) {
				strFileName = cfgObj.pdfEmailFileList
						+ formatter.format(new java.util.Date()) + i;
			} else if (activity.equals("Adhoc")) {
				strFileName = cfgObj.pdfAdEmailFileList
						+ formatter.format(new java.util.Date()) + i;
			} else if (activity.equals(BillReprintDeliver.APP_NAME
					+ BillReprintDeliver.NORMAL)) {
				formatter = new SimpleDateFormat("ddMMyyHHmm");
				strFileName = cfgObj.reprintStageDirectory + "NORM"
						+ formatter.format(new java.util.Date()) + "_" + i;
			} else if (activity.equals(BillReprintDeliver.APP_NAME
					+ BillReprintDeliver.COURIER)) {
				formatter = new SimpleDateFormat("ddMMyyHHmm");
				strFileName = cfgObj.reprintStageDirectory + "COUR"
						+ formatter.format(new java.util.Date()) + "_" + i;
			}

			if (i == intNoOfCSVFile - 1) {
				dataBean.setData(i * maxRowPerCSV, vsize, strFileName);
			} else {
				dataBean.setData(i * maxRowPerCSV, (i + 1) * maxRowPerCSV,
						strFileName);
			}
			vecStatementIds.addElement(dataBean);
		}
		return vecStatementIds;
	}

	public static ArrayList splitStatementRunIds(ArrayList stmtRunIds,
			int maxThread) {
		ArrayList arrListStatementRunIds = new ArrayList();
		int vsize = stmtRunIds.size();
		int intNoOfFile = 0;

		if (vsize % maxThread == 0) {
			intNoOfFile = vsize / maxThread;
		} else {
			intNoOfFile = (vsize / maxThread) + 1;
		}

		for (int i = 0; i < intNoOfFile; i++) {
			SplitStatementIdsDataBean dataBean = new SplitStatementIdsDataBean();

			if (i == intNoOfFile - 1) {
				dataBean.setData(i * maxThread, vsize);
			} else {
				dataBean.setData(i * maxThread, (i + 1) * maxThread);
			}
			arrListStatementRunIds.add(dataBean);
		}
		return arrListStatementRunIds;
	}

	public static Vector getAllStmtIds(ArrayList arrRunIDString)
			throws Exception {

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		StringBuffer query = new StringBuffer();
		Vector vStatementIds = new Vector();
		Vector vector = new Vector();

		try {
			Class.forName(cfgObj.databaseDriver);
			connection = DriverManager.getConnection(cfgObj.databaseSourceName,
					cfgObj.databaseUserID, cfgObj.databasePassword);

			if (connection != null) {
				query.append("SELECT S.INSTANCEID,A.EXTERNALACCOUNTKEY FROM STATEMENT S,STATEMENTRUN SR ,ACCOUNT A ");
				query.append("WHERE SR.ID=S.STATEMENTRUNID ");
				query.append("AND S.ACCOUNTID=A.ID ");
				query.append("AND SR.ID= CKFRCONVERT1.INSTANCE_TO_OBJECTID(?,4033)");

				for (int i = 0; i < arrRunIDString.size(); i++) {
					String strStatementRunId = (String) arrRunIDString.get(i);
					// prepare the statement and bind parameter
					pstmt = connection.prepareStatement(query.toString());
					pstmt.setString(1, strStatementRunId);

					rs = pstmt.executeQuery();
					String stmtId = "";
					// String acctId = "";
					String acctExternalKey = "";

					while (rs.next()) {
						System.out.println("StatementID: " + rs.getString(1));
						String[] arrVector = new String[2];
						arrVector[0] = rs.getString(1);
						arrVector[1] = rs.getString(2);
						vector.addElement(arrVector);

					}// end while
				}// end for
				pstmt.close();
				rs.close();

				PreparedStatement pstmt1 = null;
				StringBuffer query1 = new StringBuffer();

				query1.append("SELECT ACCTID,EMAILADD ");
				query1.append("FROM ACCOUNTDETAILS AD ");
				query1.append("WHERE AD.ACCTID = ? ");
				query1.append("AND TRIM(AD.EMAILDELIVERYSUBSCRIPTION) ='1' ");

				for (int j = 0; j < vector.size(); j++) {
					String[] myArr = (String[]) vector.elementAt(j);

					// prepare the statement and bind parameter
					pstmt1 = connection.prepareStatement(query1.toString());
					pstmt1.setString(1, myArr[1]);

					rs1 = pstmt1.executeQuery();

					while (rs1.next()) {
						StatementIdsDataBean stmtDataBean = new StatementIdsDataBean();
						stmtDataBean.setData(rs1.getString(1), myArr[0],
								rs1.getString(2));

						System.out.println("ACCTID: " + rs1.getString(1));
						// add to vector
						// retrieve statement id,account id,email address
						vStatementIds.addElement(stmtDataBean);
					}// end while

					pstmt1.close();
					rs1.close();
				}// end for
			}// end if
		} catch (Exception e) {
			throw new Exception("" + e);
		} finally {
			if (connection != null) {
				connection.close();
			}
			if (rs != null) {
				rs = null;
			}
			if (rs1 != null) {
				rs1 = null;
			}
		}// end try

		System.out
				.println("Get all statement Ids has been successfully executed");
		return vStatementIds;
	}

	// /////////////////////////////////////////////////////////////////////
	public static Vector getAllStmtIdsByAccountList(ArrayList accNoList)
			throws Exception {

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		StringBuffer query = new StringBuffer();
		Vector vStatementIds = new Vector();
		Vector vector = new Vector();

		try {
			Class.forName(cfgObj.databaseDriver);
			connection = DriverManager.getConnection(cfgObj.databaseSourceName,
					cfgObj.databaseUserID, cfgObj.databasePassword);

			if (connection != null) {
				for (int i = 0; i < accNoList.size(); i++) {
					String accNo = (String) accNoList.get(i);

					query.append("SELECT S.INSTANCEID,A.EXTERNALACCOUNTKEY FROM STATEMENT S ,ACCOUNT A ");
					query.append("WHERE A.EXTERNALACCOUNTKEY='" + accNo.trim()
							+ "' ");
					query.append("AND S.ACCOUNTID=A.ID ");
					query.append("AND TO_DATE(S.STATEMENTDATE,'J')='17-JUN-12'");

					// prepare the statement and bind parameter
					pstmt = connection.prepareStatement(query.toString());
					System.out.println(query.toString());
					// pstmt.setString(1,accNo);

					rs = pstmt.executeQuery();
					String stmtId = "";
					String acctExternalKey = "";

					System.out.println("accountNo:" + accNo + "***");

					while (rs.next()) {
						String[] arrVector = new String[2];
						arrVector[0] = rs.getString(1);
						arrVector[1] = rs.getString(2);
						System.out.println(arrVector[0] + "," + arrVector[1]);
						vector.addElement(arrVector);
					}// end while

					pstmt.close();
					rs.close();
					query.delete(0, query.length());
				}// end for

				PreparedStatement pstmt1 = null;
				StringBuffer query1 = new StringBuffer();

				query1.append("SELECT ACCTID,EMAILADD ");
				query1.append("FROM ACCOUNTDETAILS AD ");
				query1.append("WHERE AD.ACCTID = ? ");
				query1.append("AND TRIM(AD.EMAILDELIVERYSUBSCRIPTION) ='1' ");

				for (int j = 0; j < vector.size(); j++) {
					String[] myArr = (String[]) vector.elementAt(j);

					// prepare the statement and bind parameter
					pstmt1 = connection.prepareStatement(query1.toString());
					pstmt1.setString(1, myArr[1]);

					rs1 = pstmt1.executeQuery();

					while (rs1.next()) {
						StatementIdsDataBean stmtDataBean = new StatementIdsDataBean();
						stmtDataBean.setData(rs1.getString(1), myArr[0],
								rs1.getString(2));

						// add to vector
						// System.out.println(rs1.getString(1));

						// retrieve statement id,account id,email address
						vStatementIds.addElement(stmtDataBean);
					}// end while
					System.out.println("vStatementIds,size()"
							+ vStatementIds.size());

					pstmt1.close();
					rs1.close();
				}// end for
			}// end if
		} catch (Exception e) {
			throw new Exception("" + e);
		} finally {
			if (connection != null) {
				connection.close();
			}
			if (rs != null) {
				rs = null;
			}
			if (rs1 != null) {
				rs1 = null;
			}
		}// end try

		System.out
				.println("Get all statement Ids vy account number has been successfully executed");
		return vStatementIds;
	}

	// /////////////////////////////////////////////////////////////////////

	public static ArrayList getAllCSVStmtIds(ArrayList arrRunIDString)
			throws Exception {

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuffer query = new StringBuffer();
		ArrayList stmtIds = new ArrayList();

		try {
			Class.forName(cfgObj.databaseDriver);
			connection = DriverManager.getConnection(cfgObj.databaseSourceName,
					cfgObj.databaseUserID, cfgObj.databasePassword);

			if (connection != null) {
				query.append("SELECT S.INSTANCEID,AD.CORPCUST FROM STATEMENT S,STATEMENTRUN SR ,ACCOUNT A, ACCOUNTDETAILS AD ");
				query.append("WHERE SR.ID=S.STATEMENTRUNID ");
				query.append("AND S.ACCOUNTID=A.ID ");
				query.append("AND AD.ACCTID=A.EXTERNALACCOUNTKEY ");
				query.append("AND (TRIM(AD.CORPCUST) ='1' OR TRIM(AD.CORPCUST) ='3') ");
				query.append("AND SR.ID= CKFRCONVERT1.INSTANCE_TO_OBJECTID(?,4033)");

				for (int i = 0; i < arrRunIDString.size(); i++) {
					String strStatementRunId = (String) arrRunIDString.get(i);
					// prepare the statement and bind parameter
					pstmt = connection.prepareStatement(query.toString());
					pstmt.setString(1, strStatementRunId);

					rs = pstmt.executeQuery();

					while (rs.next()) {
						StatementIdsDataBean stmtDataBean = new StatementIdsDataBean();
						stmtDataBean.setCSVData(rs.getString(1),
								rs.getString(2));

						stmtIds.add(stmtDataBean);

					}// end while
				}// end for
				if (pstmt != null) {
					pstmt.close();
				}
				if (rs != null) {
					rs.close();
				}
			}// end if
		} catch (Exception e) {
			throw new Exception("" + e);
		} finally {
			if (connection != null) {
				connection.close();
			}
			if (rs != null) {
				rs = null;
			}
		}// end try

		System.out
				.println("Get all statement Ids has been successfully executed");
		return stmtIds;
	}

	// /////////////////////////////////////////////////////////////////////

	// /////////////////////////////////////////////////////////////////////
	public static ArrayList getAllEsmsStmtIds(ArrayList arrRunIDString)
			throws Exception {

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuffer query = new StringBuffer();
		ArrayList stmtIds = new ArrayList();

		try {
			Class.forName(cfgObj.databaseDriver);
			connection = DriverManager.getConnection(cfgObj.databaseSourceName,
					cfgObj.databaseUserID, cfgObj.databasePassword);

			if (connection != null) {
				query.append("SELECT S.INSTANCEID,AD.CORPCUST FROM STATEMENT S,STATEMENTRUN SR ,ACCOUNT A, ACCOUNTDETAILS AD ");
				query.append("WHERE SR.ID=S.STATEMENTRUNID ");
				query.append("AND S.ACCOUNTID=A.ID ");
				query.append("AND AD.ACCTID=A.EXTERNALACCOUNTKEY ");
				query.append("AND (TRIM(AD.CORPCUST) ='4') ");
				query.append("AND SR.ID= CKFRCONVERT1.INSTANCE_TO_OBJECTID(?,4033)");

				for (int i = 0; i < arrRunIDString.size(); i++) {
					String strStatementRunId = (String) arrRunIDString.get(i);
					// prepare the statement and bind parameter
					pstmt = connection.prepareStatement(query.toString());
					pstmt.setString(1, strStatementRunId);

					rs = pstmt.executeQuery();

					while (rs.next()) {
						StatementIdsDataBean stmtDataBean = new StatementIdsDataBean();
						stmtDataBean.setCSVData(rs.getString(1),
								rs.getString(2));

						stmtIds.add(stmtDataBean);

					}// end while
				}// end for
				pstmt.close();
				rs.close();
			}// end if
		} catch (Exception e) {
			throw new Exception("" + e);
		} finally {
			if (connection != null) {
				connection.close();
			}
			if (rs != null) {
				rs = null;
			}
		}// end try

		System.out
				.println("Get all statement Ids has been successfully executed");
		return stmtIds;
	}

	// /////////////////////////////////////////////////////////////////////

	public static Vector getAllSMSStmtIds(ArrayList arrRunIDString)
			throws Exception {

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuffer query = new StringBuffer();
		Vector vStatementIds = new Vector();
		Vector vector = new Vector();

		try {
			Class.forName(cfgObj.databaseDriver);
			connection = DriverManager.getConnection(cfgObj.databaseSourceName,
					cfgObj.databaseUserID, cfgObj.databasePassword);

			if (connection != null) {
				query.append("SELECT S.INSTANCEID,A.EXTERNALACCOUNTKEY,AD.SMSNO FROM STATEMENT S,STATEMENTRUN SR ,ACCOUNT A, ACCOUNTDETAILS AD ");
				query.append("WHERE SR.ID=S.STATEMENTRUNID ");
				query.append("AND S.ACCOUNTID=A.ID ");
				query.append("AND AD.ACCTID=A.EXTERNALACCOUNTKEY ");
				query.append("AND TRIM(AD.SMSSUBSCRIPTION) ='1' ");
				query.append("AND SR.ID= CKFRCONVERT1.INSTANCE_TO_OBJECTID(?,4033)");

				for (int i = 0; i < arrRunIDString.size(); i++) {
					String strStatementRunId = (String) arrRunIDString.get(i);

					// prepare the statement and bind parameter
					pstmt = connection.prepareStatement(query.toString());
					pstmt.setString(1, strStatementRunId);

					rs = pstmt.executeQuery();
					String stmtId = "";
					// String acctId = "";
					String acctExternalKey = "";

					while (rs.next()) {
						StatementIdsDataBean stmtDataBean = new StatementIdsDataBean();
						stmtDataBean.setSMSData(rs.getString(1),
								rs.getString(2), rs.getString(3));

						// add to vector
						vStatementIds.addElement(stmtDataBean);
					}// end while
				}// end for
				pstmt.close();
				rs.close();
			}// end if
		} catch (Exception e) {
			throw new Exception("" + e);
		} finally {
			if (connection != null) {
				connection.close();
			}
			if (rs != null) {
				rs = null;
			}
		}// end try
		System.out
				.println("Get all statement Ids for SMS has been successfully executed");
		return vStatementIds;
	}

}